/**
 *                            License
 * THE WORK (AS DEFINED BELOW) IS PROVIDED UNDER THE TERMS OF THIS  
 * CREATIVE COMMONS PUBLIC LICENSE ("CCPL" OR "LICENSE"). 
 * THE WORK IS PROTECTED BY COPYRIGHT AND/OR OTHER APPLICABLE LAW.  
 * ANY USE OF THE WORK OTHER THAN AS AUTHORIZED UNDER THIS LICENSE OR  
 * COPYRIGHT LAW IS PROHIBITED.
 * 
 * BY EXERCISING ANY RIGHTS TO THE WORK PROVIDED HERE, YOU ACCEPT AND  
 * AGREE TO BE BOUND BY THE TERMS OF THIS LICENSE. TO THE EXTENT THIS LICENSE  
 * MAY BE CONSIDERED TO BE A CONTRACT, THE LICENSOR GRANTS YOU THE RIGHTS CONTAINED 
 * HERE IN CONSIDERATION OF YOUR ACCEPTANCE OF SUCH TERMS AND CONDITIONS.
 * 
 */
package com.lineage.server.storage.mysql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;

import com.lineage.L1DatabaseFactory;
import com.lineage.server.model.Instance.L1PcInstance;
import com.lineage.server.storage.CharacterStorage;
import com.lineage.server.utils.SQLUtil;

/**
 * MySql的角色存储
 */
public class MySqlCharacterStorage implements CharacterStorage {

    private static Logger _log = Logger.getLogger(MySqlCharacterStorage.class
            .getName());

    /** 创建角色 */
    @Override
    public void createCharacter(final L1PcInstance pc) {
        Connection con = null;
        PreparedStatement pstm = null;
        try {
            int i = 0;
            con = L1DatabaseFactory.getInstance().getConnection();
            pstm = con
                    .prepareStatement("INSERT INTO characters SET account_name=?,objid=?,char_name=?,birthday=?,level=?,HighLevel=?,Exp=?,MaxHp=?,CurHp=?,MaxMp=?,CurMp=?,Ac=?,Str=?,Con=?,Dex=?,Cha=?,Intel=?,Wis=?,Status=?,Class=?,Sex=?,Type=?,Heading=?,LocX=?,LocY=?,MapID=?,Food=?,Lawful=?,Title=?,ClanID=?,Clanname=?,ClanRank=?,BonusStatus=?,ElixirStatus=?,ElfAttr=?,PKcount=?,PkCountForElf=?,ExpRes=?,PartnerID=?,AccessLevel=?,OnlineStatus=?,HomeTownID=?,Contribution=?,Pay=?,HellTime=?,Banned=?,Karma=?,LastPk=?,LastPkForElf=?,DeleteTime=?,RocksPrisonTime=?");
            pstm.setString(++i, pc.getAccountName());
            pstm.setInt(++i, pc.getId());
            pstm.setString(++i, pc.getName());
            pstm.setInt(++i, pc.getSimpleBirthday());
            pstm.setInt(++i, pc.getLevel());
            pstm.setInt(++i, pc.getHighLevel());
            pstm.setLong(++i, pc.getExp());
            pstm.setInt(++i, pc.getBaseMaxHp());
            int hp = pc.getCurrentHp();
            if (hp < 1) {
                hp = 1;
            }
            pstm.setInt(++i, hp);
            pstm.setInt(++i, pc.getBaseMaxMp());
            pstm.setInt(++i, pc.getCurrentMp());
            pstm.setInt(++i, pc.getAc());
            pstm.setInt(++i, pc.getBaseStr());
            pstm.setInt(++i, pc.getBaseCon());
            pstm.setInt(++i, pc.getBaseDex());
            pstm.setInt(++i, pc.getBaseCha());
            pstm.setInt(++i, pc.getBaseInt());
            pstm.setInt(++i, pc.getBaseWis());
            pstm.setInt(++i, pc.getCurrentWeapon());
            pstm.setInt(++i, pc.getClassId());
            pstm.setInt(++i, pc.get_sex());
            pstm.setInt(++i, pc.getType());
            pstm.setInt(++i, pc.getHeading());
            pstm.setInt(++i, pc.getX());
            pstm.setInt(++i, pc.getY());
            pstm.setInt(++i, pc.getMapId());
            pstm.setInt(++i, pc.get_food());
            pstm.setInt(++i, pc.getLawful());
            pstm.setString(++i, pc.getTitle());
            pstm.setInt(++i, pc.getClanid());
            pstm.setString(++i, pc.getClanname());
            pstm.setInt(++i, pc.getClanRank());
            pstm.setInt(++i, pc.getBonusStats());
            pstm.setInt(++i, pc.getElixirStats());
            pstm.setInt(++i, pc.getElfAttr());
            pstm.setInt(++i, pc.get_PKcount());
            pstm.setInt(++i, pc.getPkCountForElf());
            pstm.setInt(++i, pc.getExpRes());
            pstm.setInt(++i, pc.getPartnerId());
            pstm.setShort(++i, pc.getAccessLevel());
            pstm.setInt(++i, pc.getOnlineStatus());
            pstm.setInt(++i, pc.getHomeTownId());
            pstm.setInt(++i, pc.getContribution());
            pstm.setInt(++i, 0);
            pstm.setInt(++i, pc.getHellTime());
            pstm.setBoolean(++i, pc.isBanned());
            pstm.setInt(++i, pc.getKarma());
            pstm.setTimestamp(++i, pc.getLastPk());
            pstm.setTimestamp(++i, pc.getLastPkForElf());
            pstm.setTimestamp(++i, pc.getDeleteTime());
            pstm.setInt(++i, pc.getRocksPrisonTime());

            pstm.execute();

            _log.finest("stored char data: " + pc.getName());
        } catch (final SQLException e) {
            _log.log(Level.SEVERE, e.getLocalizedMessage(), e);
        } finally {
            SQLUtil.close(pstm);
            SQLUtil.close(con);
        }
    }

    /**
     * 删除角色.
     * 
     * @param accountName
     *            - 账号名称
     * @param charName
     *            - 角色名称
     * @throws Exception
     *             异常
     */
    @Override
    public final void deleteCharacter(final String accountName,
            final String charName) throws Exception {

        Connection con = null;
        // PreparedStatement pstm = null;
        ResultSet rs = null;
        try {
            con = L1DatabaseFactory.getInstance().getConnection();
            Statement stat = null; // Statement初始化
            try {
                stat = con.createStatement();
                rs = stat
                        .executeQuery("SELECT * FROM characters WHERE account_name='"
                                + accountName
                                + "' AND char_name='"
                                + charName
                                + "'");
                // pstm = con
                // .prepareStatement("SELECT * FROM characters WHERE account_name=? AND char_name=?");
                // pstm.setString(1, accountName);
                // pstm.setString(2, charName);
                // rs = pstm.executeQuery();
                if (!rs.next()) {
                    /*
                     * SELECTが値を返していない
                     * 存在しないか、あるいは別のアカウントが所有しているキャラクター名が指定されたということになる。
                     */
                    _log.warning("invalid delete char request: account="
                            + accountName + " char=" + charName);
                    throw new RuntimeException("could not delete character");
                }
            } catch (final Exception e) {
            } finally {
                SQLUtil.close(stat);
            }

            try {
                stat = con.createStatement();
                stat.execute("DELETE FROM character_buddys WHERE char_id IN (SELECT objid FROM characters WHERE char_name = '"
                        + charName + "')");
            } catch (final Exception e) {
            } finally {
                SQLUtil.close(stat);
            }

            // pstm = con
            // .prepareStatement("DELETE FROM character_buddys WHERE char_id IN (SELECT objid FROM characters WHERE char_name = ?)");
            // pstm.setString(1, charName);
            // pstm.execute();
            try {
                stat = con.createStatement();
                stat.execute("DELETE FROM character_buff WHERE char_obj_id IN (SELECT objid FROM characters WHERE char_name = '"
                        + charName + "')");
            } catch (final Exception e) {
            } finally {
                SQLUtil.close(stat);
            }
            // pstm = con
            // .prepareStatement("DELETE FROM character_buff WHERE char_obj_id IN (SELECT objid FROM characters WHERE char_name = ?)");
            // pstm.setString(1, charName);
            // pstm.execute();
            try {
                stat = con.createStatement();
                stat.execute("DELETE FROM character_config WHERE object_id IN (SELECT objid FROM characters WHERE char_name = '"
                        + charName + "')");
            } catch (final Exception e) {
            } finally {
                SQLUtil.close(stat);
            }

            // pstm = con
            // .prepareStatement("DELETE FROM character_config WHERE object_id IN (SELECT objid FROM characters WHERE char_name = ?)");
            // pstm.setString(1, charName);
            // pstm.execute();
            try {
                stat = con.createStatement();
                stat.execute("DELETE FROM character_items WHERE char_id IN (SELECT objid FROM characters WHERE char_name = '"
                        + charName + "')");
            } catch (final Exception e) {
            } finally {
                SQLUtil.close(stat);
            }

            // pstm = con
            // .prepareStatement("DELETE FROM character_items WHERE char_id IN (SELECT objid FROM characters WHERE char_name = ?)");
            // pstm.setString(1, charName);
            // pstm.execute();
            try {
                stat = con.createStatement();
                stat.execute("DELETE FROM character_quests WHERE char_id IN (SELECT objid FROM characters WHERE char_name = '"
                        + charName + "')");
            } catch (final Exception e) {
            } finally {
                SQLUtil.close(stat);
            }

            // pstm = con
            // .prepareStatement("DELETE FROM character_quests WHERE char_id IN (SELECT objid FROM characters WHERE char_name = ?)");
            // pstm.setString(1, charName);
            // pstm.execute();
            try {
                stat = con.createStatement();
                stat.execute("DELETE FROM character_skills WHERE char_obj_id IN (SELECT objid FROM characters WHERE char_name = '"
                        + charName + "')");
            } catch (final Exception e) {
            } finally {
                SQLUtil.close(stat);
            }

            // pstm = con
            // .prepareStatement("DELETE FROM character_skills WHERE char_obj_id IN (SELECT objid FROM characters WHERE char_name = ?)");
            // pstm.setString(1, charName);
            // pstm.execute();
            try {
                stat = con.createStatement();
                stat.execute("DELETE FROM character_teleport WHERE char_id IN (SELECT objid FROM characters WHERE char_name = '"
                        + charName + "')");
            } catch (final Exception e) {
            } finally {
                SQLUtil.close(stat);
            }

            // pstm = con
            // .prepareStatement("DELETE FROM character_teleport WHERE char_id IN (SELECT objid FROM characters WHERE char_name = ?)");
            // pstm.setString(1, charName);
            // pstm.execute();
            try {
                stat = con.createStatement();
                stat.execute("DELETE FROM characters WHERE char_name = '"
                        + charName + "'");
            } catch (final Exception e) {
            } finally {
                SQLUtil.close(stat);
            }

            // pstm = con
            // .prepareStatement("DELETE FROM characters WHERE char_name=?");
            // pstm.setString(1, charName);
            // pstm.execute();

        } catch (final SQLException e) {
            throw e;
        } finally {
            SQLUtil.close(rs);
            // SQLUtil.close(pstm);
            SQLUtil.close(con);

        }
    }

    @Override
    public L1PcInstance loadCharacter(final String charName) {
        L1PcInstance pc = null;
        Connection con = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        try {

            con = L1DatabaseFactory.getInstance().getConnection();
            pstm = con
                    .prepareStatement("SELECT * FROM characters WHERE char_name=?");
            pstm.setString(1, charName);

            rs = pstm.executeQuery();
            if (!rs.next()) {
                /*
                 * SELECT返回任何结果。
                 */
                return null;
            }
            pc = new L1PcInstance();
            pc.setAccountName(rs.getString("account_name"));
            pc.setId(rs.getInt("objid"));
            pc.setName(rs.getString("char_name"));
            pc.setBirthday(rs.getTimestamp("birthday"));
            pc.setHighLevel(rs.getInt("HighLevel"));
            pc.setExp(rs.getLong("Exp"));
            pc.addBaseMaxHp(rs.getShort("MaxHp"));
            short currentHp = rs.getShort("CurHp");
            if (currentHp < 1) {
                currentHp = 1;
            }
            pc.setCurrentHpDirect(currentHp);
            pc.setDead(false);
            pc.setStatus(0);
            pc.addBaseMaxMp(rs.getShort("MaxMp"));
            pc.setCurrentMpDirect(rs.getShort("CurMp"));
            pc.addBaseStr(rs.getShort("Str"));
            pc.addBaseCon(rs.getShort("Con"));
            pc.addBaseDex(rs.getShort("Dex"));
            pc.addBaseCha(rs.getShort("Cha"));
            pc.addBaseInt(rs.getShort("Intel"));
            pc.addBaseWis(rs.getShort("Wis"));
            final int status = rs.getInt("Status");
            pc.setCurrentWeapon(status);
            final int classId = rs.getInt("Class");
            pc.setClassId(classId);
            pc.setTempCharGfx(classId);
            pc.setGfxId(classId);
            pc.set_sex(rs.getInt("Sex"));
            pc.setType(rs.getInt("Type"));
            int head = rs.getInt("Heading");
            if (head > 7) {
                head = 0;
            }
            pc.setHeading(head);
            /*
             * int locX = resultset.getInt("locX"); int locY =
             * resultset.getInt("locY"); short map =
             * resultset.getShort("MapID"); if (locX < 30000 || locX > 40000 ||
             * locY < 30000 || locY > 40000) { locX = 32564; locY = 32955; } if
             * (map == 70) { locX = 32828; locY = 32848; } // 強制移動 short
             * moveflag = Config.RANGE_RACE_RECOGNIT; if (moveflag != 1) {
             * Random random = new Random(); // 強制移動 int rndmap = 1 +
             * random.nextInt(5); switch (rndmap) { case 1: // skt locX = 33080;
             * locY = 33392; map = 4; break; case 2: // ti locX = 32580; locY =
             * 32931; map = 0; break; case 3: // wb locX = 32621; locY = 33169;
             * map = 4; break; case 4: // kent locX = 33050; locY = 32780; map =
             * 4; break; case 5: // h locX = 33612; locY = 33268; map = 4;
             * break; default: // skt locX = 33080; locY = 33392; map = 4;
             * break; } } pc.set_x(locX); pc.set_y(locY); pc.set_map(map);
             */
            pc.setX(rs.getInt("locX"));
            pc.setY(rs.getInt("locY"));
            pc.setMap(rs.getInt("MapID"));
            pc.set_food(rs.getInt("Food"));
            pc.setLawful(rs.getInt("Lawful"));
            pc.setTitle(rs.getString("Title"));
            pc.setClanid(rs.getInt("ClanID"));
            pc.setClanname(rs.getString("Clanname"));
            pc.setClanRank(rs.getInt("ClanRank"));
            pc.setBonusStats(rs.getInt("BonusStatus"));
            pc.setElixirStats(rs.getInt("ElixirStatus"));
            pc.setElfAttr(rs.getInt("ElfAttr"));
            pc.set_PKcount(rs.getInt("PKcount"));
            pc.setPkCountForElf(rs.getInt("PkCountForElf"));
            pc.setExpRes(rs.getInt("ExpRes"));
            pc.setPartnerId(rs.getInt("PartnerID"));
            pc.setAccessLevel(rs.getShort("AccessLevel"));
            if (pc.getAccessLevel() == 200) {
                pc.setGm(true);
                pc.setMonitor(false);
            } else if (pc.getAccessLevel() == 100) {
                pc.setGm(false);
                pc.setMonitor(true);
            } else {
                pc.setGm(false);
                pc.setMonitor(false);
            }
            pc.setOnlineStatus(rs.getInt("OnlineStatus"));
            pc.setHomeTownId(rs.getInt("HomeTownID"));
            pc.setContribution(rs.getInt("Contribution"));
            pc.setPay(rs.getInt("Pay")); // 村长福利金 此栏位由 HomeTownTimeController 处理
                                         // update
            pc.setHellTime(rs.getInt("HellTime"));
            pc.setBanned(rs.getBoolean("Banned"));
            pc.setKarma(rs.getInt("Karma"));
            pc.setLastPk(rs.getTimestamp("LastPk"));
            pc.setLastPkForElf(rs.getTimestamp("LastPkForElf"));
            pc.setDeleteTime(rs.getTimestamp("DeleteTime"));
            pc.setOriginalStr(rs.getInt("OriginalStr"));
            pc.setOriginalCon(rs.getInt("OriginalCon"));
            pc.setOriginalDex(rs.getInt("OriginalDex"));
            pc.setOriginalCha(rs.getInt("OriginalCha"));
            pc.setOriginalInt(rs.getInt("OriginalInt"));
            pc.setOriginalWis(rs.getInt("OriginalWis"));
            pc.setRocksPrisonTime(rs.getInt("RocksPrisonTime"));

            pc.refresh();
            pc.setMoveSpeed(0);
            pc.setBraveSpeed(0);
            pc.setGmInvis(false);

            _log.finest("restored char data: ");
        } catch (final SQLException e) {
            _log.log(Level.SEVERE, e.getLocalizedMessage(), e);
            return null;
        } finally {
            SQLUtil.close(rs);
            SQLUtil.close(pstm);
            SQLUtil.close(con);
        }
        return pc;
    }

    /** 储存角色 */
    @Override
    public void storeCharacter(final L1PcInstance pc) {
        Connection con = null;
        PreparedStatement pstm = null;
        try {
            int i = 0;
            con = L1DatabaseFactory.getInstance().getConnection();
            pstm = con
                    .prepareStatement("UPDATE characters SET level=?,HighLevel=?,Exp=?,MaxHp=?,CurHp=?,MaxMp=?,CurMp=?,Ac=?,Str=?,Con=?,Dex=?,Cha=?,Intel=?,Wis=?,Status=?,Class=?,Sex=?,Type=?,Heading=?,LocX=?,LocY=?,MapID=?,Food=?,Lawful=?,Title=?,ClanID=?,Clanname=?,ClanRank=?,BonusStatus=?,ElixirStatus=?,ElfAttr=?,PKcount=?,PkCountForElf=?,ExpRes=?,PartnerID=?,AccessLevel=?,OnlineStatus=?,HomeTownID=?,Contribution=?,HellTime=?,Banned=?,Karma=?,LastPk=?,LastPkForElf=?,DeleteTime=? ,RocksPrisonTime=? WHERE objid=?");
            pstm.setInt(++i, pc.getLevel());
            pstm.setInt(++i, pc.getHighLevel());
            pstm.setLong(++i, pc.getExp());
            pstm.setInt(++i, pc.getBaseMaxHp());
            int hp = pc.getCurrentHp();
            if (hp < 1) {
                hp = 1;
            }
            pstm.setInt(++i, hp);
            pstm.setInt(++i, pc.getBaseMaxMp());
            pstm.setInt(++i, pc.getCurrentMp());
            pstm.setInt(++i, pc.getAc());
            pstm.setInt(++i, pc.getBaseStr());
            pstm.setInt(++i, pc.getBaseCon());
            pstm.setInt(++i, pc.getBaseDex());
            pstm.setInt(++i, pc.getBaseCha());
            pstm.setInt(++i, pc.getBaseInt());
            pstm.setInt(++i, pc.getBaseWis());
            pstm.setInt(++i, pc.getCurrentWeapon());
            pstm.setInt(++i, pc.getClassId());
            pstm.setInt(++i, pc.get_sex());
            pstm.setInt(++i, pc.getType());
            pstm.setInt(++i, pc.getHeading());
            pstm.setInt(++i, pc.getX());
            pstm.setInt(++i, pc.getY());
            pstm.setInt(++i, pc.getMapId());
            pstm.setInt(++i, pc.get_food());
            pstm.setInt(++i, pc.getLawful());
            pstm.setString(++i, pc.getTitle());
            pstm.setInt(++i, pc.getClanid());
            pstm.setString(++i, pc.getClanname());
            pstm.setInt(++i, pc.getClanRank());
            pstm.setInt(++i, pc.getBonusStats());
            pstm.setInt(++i, pc.getElixirStats());
            pstm.setInt(++i, pc.getElfAttr());
            pstm.setInt(++i, pc.get_PKcount());
            pstm.setInt(++i, pc.getPkCountForElf());
            pstm.setInt(++i, pc.getExpRes());
            pstm.setInt(++i, pc.getPartnerId());
            pstm.setShort(++i, pc.getAccessLevel());
            pstm.setInt(++i, pc.getOnlineStatus());
            pstm.setInt(++i, pc.getHomeTownId());
            pstm.setInt(++i, pc.getContribution());
            pstm.setInt(++i, pc.getHellTime());
            pstm.setBoolean(++i, pc.isBanned());
            pstm.setInt(++i, pc.getKarma());
            pstm.setTimestamp(++i, pc.getLastPk());
            pstm.setTimestamp(++i, pc.getLastPkForElf());
            pstm.setTimestamp(++i, pc.getDeleteTime());
            pstm.setInt(++i, pc.getRocksPrisonTime());
            pstm.setInt(++i, pc.getId());
            pstm.execute();
            _log.finest("储存的角色数据: " + pc.getName());
        } catch (final SQLException e) {
            _log.log(Level.SEVERE, e.getLocalizedMessage(), e);
        } finally {
            SQLUtil.close(pstm);
            SQLUtil.close(con);
        }
    }

}
